from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

app = Flask(__name__)

# MYSQL所在的主机名
host = '127.0.0.1'
# MYSQL监听的端口号
port = 3306
# 连接mysql时的用户名
username = 'root'
# 密码
password = 'root'
# mysql上创建的数据库名称
database = 'database_learn'

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{username}:{password}@{host}:{port}" \
                                        f"/{database}?charset=utf8mb4"
db = SQLAlchemy(app)


class Category(db.Model):
    """定义一个目录类"""
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(100))
    newses = db.relationship('News',back_populates="category")

    def to_dict(self):
        return {
            "id":self.id,
            "name":self.name
        }


class News(db.Model):
    """定义一个新闻类"""
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    title = db.Column(db.String(100))
    content = db.Column(db.Text)
    category_id = db.Column(db.Integer,db.ForeignKey("category.id"))
    category = db.relationship("Category",back_populates='newses')

    def to_dict(self):
        return {
            "id":self.id,
            "title":self.title,
            "content":self.content,
            "category_id":self.content
        }


with app.app_context():
    db.create_all()


@app.route("/newses")
def newses():
    newses = News.query.all()
    news_list = []
    for news in newses:
        news_list.append(news.to_dict())
    return news_list


@app.route("/add/category")
def add_cate():
    category = Category(name="体育")
    db.session.add(category)
    db.session.commit()
    return "添加成功"


@app.route("/add/news")
def add_news():
    category = Category(name='体育')
    news = News(title="标题1",content='内容1')
    news.category = category
    db.session.add(news)
    db.session.commit()

    return "新闻添加成功"


@app.route("/")
def hello():
    return "hello world"


@app.route("/cates")
def cate():
    cates = Category.query.all()
    data = []
    for cate in cates:
        data.append(cate.to_dict())
    return data


if __name__ == '__main__':
    app.run(port=8888,debug=True)